package org.boot.easyapi.core.util;

import com.github.xiaoymin.knife4j.core.util.StrUtil;
import org.boot.easyapi.core.cache.CacheRedisDAO;
import org.boot.easyapi.core.dao.dto.param.OrderParamDTO;
import org.boot.easyapi.core.dao.dto.param.PageParamDTO;
import org.boot.easyapi.core.dao.dto.param.RequestSqlParamsDTO;
import org.boot.easyapi.core.dao.dto.param.AndParamDTO;
import org.boot.easyapi.core.dao.dto.sql.SqlSelectLastMapDTO;
import org.boot.easyapi.util.jdbc.JdbcUtil;
import org.boot.easyapi.core.util.sql.SqlOperatorUtil;
import org.boot.easyapi.enums.db.DbEnum;
import org.boot.easyapi.pojo.db.TableDTO;
import org.boot.easyapi.util.CollUtil;
import org.boot.easyapi.util.ObjectUtil;
import org.boot.easyapi.util.spring.SpringUtil;

import java.util.*;

public final class QueryParser {

    /** 需要提前拿走、不参与 SQL 拼接的控制字段 */
    private static final Set<String> DEFAULT_CTRL = Collections.unmodifiableSet(
            new HashSet<>(Arrays.asList(
                    "pageNo", "page_no", "pageSize", "page_size",
                    "respKey", "resp_key",
                    "configId", "config_id"
            )));

    private static final String OP_AT = "@";

    /**
     * 解析入参Map
     *
     * @param tableName 表名
     * @param params    前端传过来的所有参数
     * @return 分页信息 + 条件列表
     */
    public static RequestSqlParamsDTO parse(String tableName, Map<String, Object> params) {
        PageParamDTO page = new PageParamDTO();
        OrderParamDTO order = new OrderParamDTO();
        List<AndParamDTO> list = new ArrayList<>();

        params.forEach((key, v) -> {
            if (v == null || StrUtil.isBlank(v.toString())) {
                return; // 空值直接忽略
            }

            // 1. 控制字段
            if (DEFAULT_CTRL.contains(key)) {
                if ("pageNo".equals(key) || "page_no".equals(key)) {
                    page.setPageNo(Integer.parseInt(v == null ? "1" : v.toString()));
                } else if ("pageSize".equals(key) || "page_size".equals(key)) {
                    page.setPageSize(Integer.parseInt(v == null ? "10" : v.toString()));
                }
                return;
            }

            // 2. 解析字段名与操作符
            String field, op, sort;
            int at = key.lastIndexOf(OP_AT);
            if (at == -1) {
                // 默认判定
                field = key;
                op = "EQ";
                if (!tableColumnExists(tableName, field)) throw new IllegalArgumentException("表 " + tableName + " 不存在字段：" + field);
            } else {
                //逻辑判定
                field = key.substring(0, at);
                op = key.substring(at + 1).toUpperCase();
                if (!tableColumnExists(tableName, field)) throw new IllegalArgumentException("表 " + tableName + " 不存在字段：" + field);
                if (SqlOperatorUtil.OPSMAP.get(op) == null) throw new IllegalArgumentException("不支持的操作符：" + op);
                if (op.equals("ASC") || op.equals("DESC")){
                    order.setOrderBy(op);
                    order.setField(field);
                    return;
                }
            }
            list.add(new AndParamDTO(field, op, v));
        });

        return new RequestSqlParamsDTO(page, order, list);
    }




    /**
     * 根据 ParseResult 生成预编译 SQL 及参数数组
     *
     * @param params
     * @param isPage  是否分页
     * @return 0=SQL  1=Object[] 参数
     */
    public static SqlSelectLastMapDTO build(RequestSqlParamsDTO params, boolean isPage) {
        if(ObjectUtil.isEmpty(params)) return new SqlSelectLastMapDTO(null, null);

        StringBuilder sql = new StringBuilder();
        List<Object> argList = new ArrayList<>();

        /* 1. WHERE */
        List<AndParamDTO> whereList = params.getWhereParam();
        if (!whereList.isEmpty()) {
            for (int i = 0; i < whereList.size(); i++) {
                AndParamDTO w = whereList.get(i);
                if(ObjectUtil.isEmpty(w)){
                    continue;
                }
                String op;
                List<?> inVals;
                sql.append(" AND ");
                switch (w.getOp()) {
                    case "LIKE":
                        op = SqlOperatorUtil.OPSMAP.get(w.getOp());
                        if(JdbcUtil.checkDbType().equals(DbEnum.MYSQL)){
                            sql.append(w.getField()).append(op).append(" CONCAT('%',?,'%') ");
                        }else if(JdbcUtil.checkDbType().equals(DbEnum.POSTGRESQL)){  //kingbase、postgresql
                            sql.append(w.getField()).append(op).append(" '%' || ? || '%' ");
                        }
                        argList.add(w.getValue());
                        break;
                    case "ISNULL":
                        op = SqlOperatorUtil.OPSMAP.get(w.getOp());
                        sql.append(w.getField()).append(op).append(" IS NULL ");
                        break;
                    case "NOTNULL":
                        op = SqlOperatorUtil.OPSMAP.get(w.getOp());
                        sql.append(w.getField()).append(op).append(" IS NOT NULL ");
                        break;
                    case "IN":
                        op = SqlOperatorUtil.OPSMAP.get(w.getOp());
                        inVals = CollUtil.toStrList(w.getValue().toString());
                        sql.append(w.getField())
                                .append(op)
                                .append(" (")
                                .append(repeatQuestion(inVals.size()))
                                .append(")");
                        argList.addAll(inVals);
                        break;
                    case "NOTIN":
                        op = SqlOperatorUtil.OPSMAP.get(w.getOp());
                        inVals = CollUtil.toStrList(w.getValue().toString());
                        sql.append(w.getField())
                                .append(op)
                                .append(" (")
                                .append(repeatQuestion(inVals.size()))
                                .append(")");
                        argList.addAll(inVals);
                        break;
                    default:
                        op = SqlOperatorUtil.OPSMAP.get(w.getOp());
                        sql.append(w.getField())
                                .append(op)
                                .append(" ?");
                        argList.add(w.getValue());
                }
            }
        }
        /* 2. ORDER BY */
        OrderParamDTO order = params.getOrderParam();
        if (order != null && order.getField() != null) {
            sql.append(" ORDER BY ")
                    .append(order.getField())
                    .append(" ")
                    .append(order.getOrderBy()); // DESC/ASC
        }

        /* 3. LIMIT */
        if(isPage){
            PageParamDTO page = params.getPageParam();
            if(ObjectUtil.isNotEmpty(page)){
                sql.append(" LIMIT ? OFFSET ? ");
                argList.add(page.getPageSize());
                argList.add((page.getPageNo() - 1) * page.getPageSize());
            }
        }
        return new SqlSelectLastMapDTO(sql.toString(), argList.toArray());
    }

    /* 生成 ?,?,? */
    private static String repeatQuestion(int n) {
        StringBuilder sb = new StringBuilder(n * 2 - 1);
        for (int i = 0; i < n; i++) {
            if (i > 0) sb.append(',');
            sb.append('?');
        }
        return sb.toString();
    }

    private static boolean tableColumnExists(String tableName, String fieldName) {
        CacheRedisDAO cacheRedisDAO = SpringUtil.getBean(CacheRedisDAO.class);
        List<TableDTO> table = cacheRedisDAO.getTable(tableName, true);
        if(CollUtil.isEmpty(table)){
            throw new IllegalArgumentException("表 " + tableName + " 不存在");
        }
        for (TableDTO tableDTO : table) {
            if(tableDTO.getName().equals(fieldName)){
                return true;
            }
        }
        return false;
    }
}